import datetime
import os
import sys
import pandas as pd
from smart_open import open
from pandas import NaT
import json
import boto3

from aws_model.athena_submit import submit

sys.path.append(os.path.dirname(sys.path[0]))
from aws_model.aws_stepfunc import send_task_failure, send_task_success
from base_class.inspect_logging import print_to_log
from config import *
from base_class import time_util
from project_utils.mysql_conncet import ReadFromMysql

mysql = ReadFromMysql(host=mysqlhost, user=user, password=password, port=3306)
mysql.makepool("air_bigdata")

extract = []
mapping = {}


def import_excel(source_file: str, target_dir, sheets, import_type, version_desc):
    # 读取映射表
    rs = mysql.read_from_mysql(
        f''' select source_field,target_field,extract_params from import_mapping where type = '{import_type}' and enabled = 1 and version = '{version_desc}' ''')
    print_to_log('input:', source_file, target_dir, sheets, import_type, version_desc)

    # 映射转换为mapping
    for one in rs:
        mapping[one['source_field']] = one['target_field']
        if one['source_field'] == 'extract':
            extract.extend(one['extract_params'].split(','))
    print_to_log('mapping:', mapping)
    if not mapping:
        return

    # 导入每一个sheet
    for sheet in sheets.split(","):
        # 如果源文件为s3，构建s3配置
        s3options = {}
        if source_file.startswith("s3"):
            s3options = {
                "client_kwargs": {"region_name": FORMAL_S3_CONFIG['region_name']},
                "key": FORMAL_S3_CONFIG["access_key"],
                "secret": FORMAL_S3_CONFIG["secret_key"],
            }

        # 读取源文件
        df = pd.read_excel(source_file, sheet_name=sheet, storage_options=s3options).fillna('')
        pd.set_option('display.max_columns', 1000)
        pd.set_option('display.width', 1000)  # 设置宽度,就是说不换行,比较好看数据
        pd.set_option('display.max_rows', 500)  # 设置行数的多少

        # 删除没有映射的多余列
        drop_keys = []
        for key in df.keys():
            if not key in mapping:
                drop_keys.append(key)
        df = df.drop(labels=drop_keys, axis=1)
        # 修改列名为映射后的列名
        df.rename(columns=mapping, inplace=True)
        # 提取生产时间
        df = df.apply(change, axis=1)
        # dataframe转换为dict
        jsons = df.to_dict(orient="records")

        # 目标文件的前缀名
        prefix = source_file.split("/")[-1]
        # 目标文件地址全名
        file_name = f"{target_dir}{prefix}-{sheet}.json.gz"
        # 写出时的配置，主要用于s3
        transport_params = {}
        if file_name.startswith("s3"):
            session = boto3.Session(region_name=FORMAL_S3_CONFIG['region_name'],
                                    aws_access_key_id=FORMAL_S3_CONFIG["access_key"],
                                    aws_secret_access_key=FORMAL_S3_CONFIG["secret_key"])
            transport_params = {'client': session.client("s3")}

        # 写出通道
        with open(file_name, 'w', compression='.gz', encoding='utf-8', transport_params=transport_params) as fw:
            length = len(jsons)
            for i in range(0, length):
                one = jsons[i]
                for key in list(one.keys()):
                    # 过滤掉为空的
                    if one[key] == '' or one[key] is NaT:
                        del one[key]
                    # 将时间字段转换成字符串
                    elif key.endswith('_time'):
                        t = one[key]
                        if isinstance(t, str):
                            one[key] = str(time_util.strp_time(t))
                        else:
                            one[key] = str(t)
                    # 转换成字符串
                    else:
                        one[key] = str(one[key])
                if i == length - 1:
                    fw.write(json.dumps(one, ensure_ascii=False))
                else:
                    fw.write(json.dumps(one, ensure_ascii=False) + "\n")
        print_to_log("导入文件:", source_file, ",sheet:", sheet, '完成')
    print_to_log("done,导入文件:", source_file, '完成')


# 提取生产日期
def change(new_data):
    if len(extract) > 0:
        nj_data = ''
        wj_data = ''
        az_data = ''
        # 读取内机、外机、判断时间
        nj = str(new_data[extract[0]])
        wj = str(new_data[extract[1]])
        time = str(new_data[extract[2]])
        if len(nj) >= 21:
            nj_data = nj[15:20]
        if len(wj) >= 21:
            wj_data = wj[15:20]
        if time:
            # 如果时间为datatime，代表转换成功。
            trans_time = time_util.strp_time(time)
            if isinstance(trans_time, datetime.datetime):
                az_data = str(trans_time)[0:10]
        #         print(v['new_zc_time'][i])

        # I->1 , O->0, Z -> 2
        # 如果五位数字中存在此部分数据，则需进行替换

        data = ''
        if nj_data:
            data = nj_data
        elif wj_data:
            data = wj_data
        if data:
            try:
                zc_data = ''
                th_dict = {'I': '1', 'O': '0', 'Z': '2'}
                for k, v in th_dict.items():
                    if k in nj_data:
                        data = data.replace(k, v)
                # 如果内机日期数和外机日期数中的存在字母，则采用2020年4月后解析方式
                new_data_dic = {'A': '10', 'B': '11', 'C': '12'}
                if data[2].isalpha():
                    Year1 = data[0:2]
                    Month1 = data[2:3]
                    if Month1.isalpha():
                        Month1 = new_data_dic[Month1]
                    Date1 = data[3:5]
                    zc_data = '20' + Year1 + '-' + Month1 + '-' + Date1
                # 如果第二、三位合并大于12，必为新版
                elif int(data[1:3]) > 12:
                    Year1 = data[0:2]
                    Month1 = data[2:3]
                    Date1 = data[3:5]
                    zc_data = '20' + Year1 + '-' + '0' + Month1 + '-' + Date1
                # 当第三位为0时，必为老版
                elif int(data[2]) == 0 and az_data:
                    Year1 = data[0:1]
                    Month1 = data[1:3]
                    Data1 = data[3:5]
                    if int(az_data[0:3] + Year1 + Month1 + Data1) < int(az_data.replace('-', '')):
                        if int(az_data[0:3] + Year1 + Month1) > 202004:
                            zc_data = '注册时间未知'
                        else:
                            zc_data = az_data[0:3] + Year1 + '-' + Month1 + '-' + Data1
                    else:
                        zc_data = str(int(az_data[0:3]) - 1) + Year1 + '-' + Month1 + '-' + Data1
                # 当第一位不等于2时，则必为老版（可运行到2030年）
                elif int(data[0]) != 2 and az_data:
                    Year1 = data[0:1]
                    Month1 = data[1:3]
                    Data1 = data[3:5]
                    if int(az_data[0:3] + Year1 + Month1 + Data1) < int(az_data.replace('-', '')):
                        if int(az_data[0:3] + Year1 + Month1) > 202004:
                            zc_data = '注册时间未知'
                        else:
                            zc_data = az_data[0:3] + Year1 + '-' + Month1 + '-' + Data1
                    else:
                        zc_data = str(int(az_data[0:3]) - 1) + Year1 + '-' + Month1 + '-' + Data1
                # 通过两套规则进行解析，取距离安装日期最近的值
                # 第一套符合2套规则：第一位：2  第二位：0  第三位：1--9
                # 第二套符合2套规则：第一为：2  第二位：1  第三位：1--2
                elif az_data:
                    # 定义一个储存日期的列表
                    date_list = []

                    # 通过通过新的解析规则进行解析
                    Year1 = data[0:2]
                    Month1 = data[2:3]
                    Date1 = data[3:5]

                    if int('20' + Year1 + '0' + Month1 + Date1) <= int(az_data.replace('-', '')) and int(
                            '20' + Year1 + '0' + Month1) >= 202004:
                        zc_data1 = '20' + Year1 + '-' + '0' + Month1 + '-' + Date1
                        date_list.append(zc_data1)

                    # 通过老规则进行数据解析
                    Year2 = data[0:1]
                    Month2 = data[1:3]
                    Data2 = data[3:5]
                    if int(az_data[0:3] + Year2 + Month2 + Data2) < int(az_data.replace('-', '')):
                        if int(az_data[0:3] + Year2 + Month2) < 202004:
                            zc_data2 = az_data[0:3] + Year2 + '-' + Month2 + '-' + Data2
                            date_list.append(zc_data2)
                    else:
                        zc_data2 = str(int(az_data[0:3]) - 1) + Year2 + '-' + Month2 + '-' + Data2
                        date_list.append(zc_data2)

                    # 判断输出那个时间为注册时间
                    if len(date_list) < 1:
                        zc_data = '注册时间未知'
                    elif len(date_list) == 1:
                        zc_data = date_list[0]
                    else:
                        diff1 = int(az_data.replace('-', '')) - int(date_list[0].replace('-', ''))
                        diff2 = int(az_data.replace('-', '')) - int(date_list[1].replace('-', ''))
                        if diff1 > diff2:
                            zc_data = date_list[1]
                        else:
                            zc_data = date_list[0]
                #         print(v['new_zc_time'])
                new_data[mapping['extract']] = zc_data
            except Exception as e:
                print_to_log('error:' + str(e))
    return new_data


'''
传入参数:
{
 "source":源文件地址，定位到具体文件, C://Users/DELL/Desktop/虹美/快益点压缩文件
 "target":目标地址，定位到文件夹，需要带文件类型，比如本地文件系统需要前缀 file:// s3需要 s3://,
 "sheets":excel需要导入的sheet名，多个sheet 逗号隔开,
 "type":映射类型，对应import_mapping表中的 type，现有 kyd,ac365,
 "version":版本，对应import_mapping表中version，用于过滤,
}
'''
if __name__ == '__main__':
    input = json.loads(sys.argv[1])
    if 'input' not in input or not input['version']:
        version = 'default'
    else:
        version = input['version']
    import_excel(input['source'], input['target'], input['sheets'], input['type'], version)